﻿using ImageManager.Common;
using ImageManager.Model;
using ImageManager.MyExcep;
using ImageManager.MyType;
using System.Collections;
using System.Data;
using System.Reflection;

namespace ImageManager.DBAccess
{
    public class DbFuzzyQuery
    {
        List<PropValueSetter> PropSetters;
        string tableName;
        Type type;
        DbInstance instance;
        List<OrderByField> orderFields = new List<OrderByField>();
        List<WhereConditional> whereFields = new List<WhereConditional>();
        public DbFuzzyQuery(DbInstance instance, Type type)
        {
            this.instance = instance;
            this.type = type;
            var tabname = type.GetCustomAttribute<MyTableNameAttribute>()?.TableName;
            if (tabname == null) throw new MyNotSupportException($"{type.Name}不支持FuzzyQuery");
            tableName = tabname!;
            PropSetters = DataAccessUtils.GetSetters(type);
        }
        public DbFuzzyQuery Where(CondCollection whereConditionals)
        {
            whereFields.AddRange(whereConditionals.ToConds());
            return this;
        }
        public DbFuzzyQuery OrderBy(params OrderByField[] orderBy)
        {
            orderFields.AddRange(orderBy);
            return this;
        }
        private string ToSql(string select_part)
        {
            string where_path = " WHERE DEL = 'N' ";
            foreach (var item in whereFields)
            {
                where_path += item.ToSql();
            }
            string order_path = " ORDER BY ";
            if (orderFields.Count == 0) order_path += " 1 DESC,";
            foreach (var item in orderFields)
            {
                order_path += $"{item.Field},";
            }
            order_path = order_path.Substring(0, order_path.Length - 1);
            var sql = select_part + where_path + order_path;
            return sql;
        }
        private ArrayList ReadAsList(IDataReader reader)
        {
            ArrayList result = new ArrayList();
            while (reader.Read())
            {
                object record = type.Assembly.CreateInstance(type.FullName!)!;
                foreach (var setter in PropSetters)
                {
                    setter.Set(reader, record);
                }
                result.Add(record);
            }
            reader.Close();
            return result;
        }
        private IDataReader MakeReader(string sql)
        {
            Dictionary<string, object?> p = new Dictionary<string, object?>();
            foreach (var field in whereFields)
            {
                if (!string.IsNullOrWhiteSpace(field.ParamName))
                {
                    p.Add(field.ParamName, field.Value);
                }
            }
            var cmd = instance.MakeQueryCommand(sql, p.ToArray());
            var dt1 = DateTime.Now;
            var r = cmd.ExecuteReader();
            var cost = (DateTime.Now - dt1).TotalMilliseconds;
            MyLogger.Sql("EXEC SQL={0} COST={1}ms", cmd.CommandText, cost);
            whereFields.Clear();
            orderFields.Clear();
            return r;
        }

        public ArrayList ToList()
        {
            string slt_part = string.Format("SELECT * FROM \"{0}\"  ", tableName);
            var sql = ToSql(slt_part);
            var reader = MakeReader(sql);
            return ReadAsList(reader);
        }
        public int Count()
        {
            string slt_part = string.Format("SELECT COUNT(*) FROM \"{0}\"  ", tableName);
            string sql = ToSql(slt_part);
            using (var reader = MakeReader(sql))
            {
                if (reader.Read())
                {
                    return reader.GetInt32(0);
                }
            }
            throw new MyDbConnectException("无法执行语句" + sql);
        }
        public object? First()
        {
            string slt_part = string.Format("SELECT TOP 1 * FROM \"{0}\"  ", tableName);
            var sql = ToSql(slt_part);
            var reader = MakeReader(sql);
            var result = ReadAsList(reader);
            if (result.Count == 0) return null;
            else return result[0];

        }
        public object? Get(string pk)
        {
            string sql = string.Format("SELECT * FROM \"{0}\"  WHERE DEL = 'N' AND PK = :PK", tableName);
            var cmd = instance.MakeQueryCommand(sql, new KeyValuePair<string, object?>(":PK", pk));
            var result = ReadAsList(cmd.ExecuteReader());
            if (result.Count == 0) return null;
            else return result[0];
        }
        public object? GetField(string field, string pk)
        {
            //根据一个主键以及一个字段返回一个结果
            var record = Where(CondCollection.True().Eq(nameof(BaseModel.Pk), pk)).First();
            var result = type.GetProperty(field)?.GetValue(record);
            return result;
        }
        public List<T> Select<T>(string field)
        {
            string slt_part = string.Format("SELECT {1} FROM \"{0}\"  ", tableName, field);
            var sql = ToSql(slt_part);
            List<T> array = new List<T>();
            using (var reader = MakeReader(sql))
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        array.Add((T)reader.GetValue(0));
                    }
                }
                return array;
            }
        }
    }
}